import threading
import traceback
from urllib.parse import quote_plus

import sqlalchemy
from sqlalchemy import text, create_engine
from sqlalchemy.schema import CreateTable
from sqlalchemy.orm import Session

class DataBase:
    lock = threading.Lock()
    engine = None
    @classmethod
    def get_engin(cls):
        cls.lock.acquire()
        if not cls.engine:
            cls.engine = create_engine(f"mysql+pymysql://root:{quote_plus('suyoe3QgC3OFdUF')}@release.mysql.epochingai.com:3306/demo?charset=utf8mb4", pool_recycle = 360)
        cls.lock.release()
        return cls.engine

class MySqlUtil:

    @staticmethod
    def get_table(table_name):
        """
        获取某个表的表对象
        :param table_name:
        :return:
        """
        metadata = sqlalchemy.MetaData()
        metadata.reflect(DataBase.get_engin())
        table = sqlalchemy.Table(table_name, metadata)
        return table

    @staticmethod
    def execute_sql(sql, param=None):
        """
        执行sql语句
        :param sql:
        :param param:
        :return:
        """
        session = Session(bind=DataBase.get_engin())
        try:
            res = session.execute(text(sql), param).all()
            session.commit()
            return res
        finally:
            session.close()

    @staticmethod
    def execute_sql_ori(sql, param=None):
        """
        执行sql语句
        :param sql:
        :param param:
        :return:
        """
        session = Session(bind=DataBase.get_engin())
        try:
            res = session.execute(text(sql), param)
            session.commit()
            return res
        finally:
            session.close()

    @staticmethod
    def bantch_execut(sqls):
        """
        执行sql语句
        :param sql:
        :param param:
        :return:
        """
        session = Session(bind=DataBase.get_engin())
        try:
            for sql in sqls:
                session.execute(text(sql[0]), sql[1] if len(sql) == 2 else None)
            session.commit()
        except:
            traceback.print_exc()
            session.rollback()
        finally:
            session.close()



    @staticmethod
    def get_columns(table):
        """
        获取表的所有字段
        :param table:
        :return:
        """
        return table.columns

    @staticmethod
    def get_schema_names():
        """
        获取约束名称
        :return:
        """
        insp = sqlalchemy.inspect(DataBase.get_engin())
        return insp.get_schema_names()

    @staticmethod
    def get_tables():
        """
        获取所有表名
        :return:
        """
        metadata = sqlalchemy.MetaData()
        metadata.reflect(DataBase.get_engin())
        return metadata.tables.values()

    @staticmethod
    def get_create_table(table):
        """
        获取表的create table语句
        :param table:
        :return:
        """
        return CreateTable(table)


    @staticmethod
    def resultset2listmap(resultset):
        """
        搜索结果集转列表对象
        :param resultset:
        :return:
        """
        result = []
        if resultset:
            for item in resultset:
                result.append({k: v for k, v in item._mapping.items()})
        return result


if __name__ == '__main__':
    res = MySqlUtil.execute_sql(f"select count(1) from spider_product_result where link = 'https://www.aliexpress.us/item/3256806439491185.html?spm=a2g0o.productlist.main.3.e8984Mv44Mv44O&algo_pvid=22060c77-078a-4fce-a2f7-7a1418193f7a&algo_exp_id=22060c77-078a-4fce-a2f7-7a1418193f7a-1&pdp_npi=4%40dis%21USD%2113.28%211.38%21%21%2196.30%2110.02%21%40213d7a8f17217019272976612e87c8%2112000037859253680%21sea%21US%216051425743%21ABX&curPageLogUid=y8hSUIZqmRxP&utparam-url=scene%3Asearch%7Cquery_from%3A'").__next__()
    if res[0] > 0:
        print("hahaha")
    else:
        print("aaaaa")